package com.efast.cafe.framework.util;

import java.io.ByteArrayOutputStream;
import java.io.PrintStream;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.LinkedList;
import java.util.List;
import java.util.Set;
import java.util.StringTokenizer;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import com.efast.cafe.portal.entity.query.Template;

import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.statement.Statement;

public class SqlFormateUtil {
	private static final Set<String> BEGIN_CLAUSES = new HashSet<String>();
	private static final Set<String> END_CLAUSES = new HashSet<String>();
	private static final Set<String> LOGICAL = new HashSet<String>();
	private static final Set<String> QUANTIFIERS = new HashSet<String>();
	private static final Set<String> DML = new HashSet<String>();
	private static final Set<String> MISC = new HashSet<String>();
	public static final String SUCCESS_MESSAGE = "correct";
	public static final String DELETED_MESSAGE = "Condition have been deleted";
	public static String prefix="template_p_";
	public static String suffix="_p_template";

	static final String indentString = "    ";
	static final String initial = "\n    ";
	public static List<String> whereItems = null;//获得where 里面的过滤条件
	public static List<String> conditionItems = null;//获得where 里面的过滤条件
	public static String selectArea;//获得格式化的模板 selectArea内容
	public static String fromArea;//获得格式化的模板 fromArea内容
	public static String groupByArea;//获得格式化的模板 groupByArea内容
	public static String orderByArea;//获得格式化的模板 orderByArea内容

	public static String format(String source) {
		FormatProcess formatProcess = new FormatProcess(source);
		String sql = formatProcess.perform().trim();
		whereItems = formatProcess.getWhereItems();
		conditionItems = formatProcess.getConditionItems();
		selectArea = formatProcess.getSelectArea().toString();
		fromArea = formatProcess.getFromArea().toString();
		groupByArea = formatProcess.getGroupByArea() == null? "" :formatProcess.getGroupByArea().toString();
		orderByArea = formatProcess.getOrderByArea() == null? "" :formatProcess.getOrderByArea().toString();
		return sql;
	}

	static {
		BEGIN_CLAUSES.add("left");
		BEGIN_CLAUSES.add("right");
		BEGIN_CLAUSES.add("inner");
		BEGIN_CLAUSES.add("outer");
		BEGIN_CLAUSES.add("group");
		BEGIN_CLAUSES.add("order");

		END_CLAUSES.add("where");
		END_CLAUSES.add("set");
		END_CLAUSES.add("having");
		END_CLAUSES.add("join");
		END_CLAUSES.add("from");
		END_CLAUSES.add("by");
		END_CLAUSES.add("join");
		END_CLAUSES.add("into");
		END_CLAUSES.add("union");

		LOGICAL.add("and");
		LOGICAL.add("or");
		LOGICAL.add("when");
		LOGICAL.add("else");
		LOGICAL.add("end");

		QUANTIFIERS.add("in");
		QUANTIFIERS.add("all");
		QUANTIFIERS.add("exists");
		QUANTIFIERS.add("some");
		QUANTIFIERS.add("any");

		DML.add("insert");
		DML.add("update");
		DML.add("delete");

		MISC.add("select");
		MISC.add("on");
	}

	private static class FormatProcess {
		boolean beginLine = true;
		boolean afterBeginBeforeEnd = false;
		boolean afterByOrSetOrFromOrSelect = false;
		boolean afterValues = false;
		boolean afterOn = false;
		boolean afterBetween = false;
		boolean afterInsert = false;
		int fromTime =0;
		int inFunction = 0;
		int parensSinceSelect = 0;
		private LinkedList<Integer> parenCounts = new LinkedList<Integer>();
		private LinkedList<Boolean> afterByOrFromOrSelects = new LinkedList<Boolean>();
		private List<String> whereItems = null;
		private List<String> conditionItems = new ArrayList<>();
		boolean isWhereStart = false;
		boolean isItemStartForWhere = false;
		boolean isItemStartForSelect = false;
		boolean isNewLineForWhere = false;
		StringBuffer selectArea = new StringBuffer();
		StringBuffer fromArea =  new StringBuffer();
		StringBuffer groupByArea = null;
		StringBuffer orderByArea = null;
		boolean isSelectStart = false;
		boolean selectInit = true;
		boolean isFromStart = false;
		boolean isGroupByStart = false;
		boolean isOrderByStart = false;
		int indent = 1;

		StringBuffer result = new StringBuffer();
		StringTokenizer tokens;
		String lastToken;
		String token;
		String lcToken;

		public FormatProcess(String sql) {
			this.tokens = new StringTokenizer(sql, "()+*/-=<>'`\"[], \n\r\f\t", true);
		}

		public String perform() {
			this.result.append("\n    ");

			while (this.tokens.hasMoreTokens()) {
				this.token = this.tokens.nextToken();
				this.lcToken = this.token.toLowerCase();
				if ("'".equals(this.token)) {
					String t;
					do {
						t = this.tokens.nextToken();
						this.token += t;
					} while ((!"'".equals(t)) && (this.tokens.hasMoreTokens()));
				} else if ("\"".equals(this.token)) {
					String t;
					do {
						t = this.tokens.nextToken();
						this.token += t;
					} while (!"\"".equals(t));
				}

				if ((this.afterByOrSetOrFromOrSelect) && (",".equals(this.token))) {
					commaAfterByOrFromOrSelect();
				} else if ((this.afterOn) && (",".equals(this.token))) {
					commaAfterOn();
				} else if ("(".equals(this.token)) {
					if (isWhereStart) {
						isItemStartForWhere = true;
					} else if (selectInit) {
						isItemStartForSelect = true;
					}
					openParen();
				} else if (")".equals(this.token)) {
					closeParen();
					if (isWhereStart) {
						isItemStartForWhere = false;
					} else if (selectInit) {
						isItemStartForSelect = false;
					}
				} else if (SqlFormateUtil.BEGIN_CLAUSES.contains(this.lcToken)) {
					if (this.lcToken.equals("group")) {
						isGroupByStart = true;
						groupByArea = new StringBuffer();
					} else {
						isGroupByStart = false;
					}
					if (this.lcToken.equals("order")) {
						isOrderByStart = true;
						orderByArea = new StringBuffer();
					} else {
						isOrderByStart = false;
					}
					beginNewClause();
				} else if (SqlFormateUtil.END_CLAUSES.contains(this.lcToken)) {
					if (this.lcToken.equals("where")) {

					} else if (this.lcToken.equals("from")) {
						fromTime +=1;
						if (fromTime == 0) {
							selectInit = false;
							isFromStart = true;
						}
					}
					endNewClause();
				} else if ("select".equals(this.lcToken)) {
					if (this.lcToken.equals("select")) {
						isSelectStart = true;
						fromTime -=1;
					}
					select();
				} else if (SqlFormateUtil.DML.contains(this.lcToken)) {
					updateOrInsertOrDelete();
				} else if ("values".equals(this.lcToken)) {
					values();
				} else if ("on".equals(this.lcToken)) {
					on();
				} else if ((this.afterBetween) && (this.lcToken.equals("and"))) {
					misc();
					this.afterBetween = false;
				} else if (SqlFormateUtil.LOGICAL.contains(this.lcToken)) {
					if (isWhereStart && (this.lcToken.equals("and") || this.lcToken.equals("or"))) {
						if (isItemStartForWhere) {
							isNewLineForWhere = false;
						} else {
							isNewLineForWhere = true;
						}
					}
					logical();
				} else if (isWhitespace(this.token)) {
					//if (!isItemStartForSelect && !isItemStartForWhere) {
					white();
					//}

				} else {
					misc();
				}

				if (!isWhitespace(this.token)) {
					this.lastToken = this.lcToken;
				}
			}

			return this.result.toString();
		}

		private void commaAfterOn() {
			out();
			if (!isItemStartForWhere && !isItemStartForSelect) {
				this.indent -= 1;
				newline();
			}
			this.afterOn = false;
			this.afterByOrSetOrFromOrSelect = true;
		}

		private void commaAfterByOrFromOrSelect() {
			out();
			if (!isItemStartForWhere && !isItemStartForSelect) {
				newline();
			}

		}

		private void logical() {
			if ("end".equals(this.lcToken)) {
				this.indent -= 1;
			}
			newline();
			out();
			this.beginLine = false;
		}

		private void on() {
			this.indent += 1;
			this.afterOn = true;
			newline();
			out();
			this.beginLine = false;
		}

		private void misc() {
			out();
			if ("between".equals(this.lcToken)) {
				this.afterBetween = true;
			}
			if (this.afterInsert) {
				newline();
				this.afterInsert = false;
			} else {
				this.beginLine = false;
				if ("case".equals(this.lcToken))
					this.indent += 1;
			}
		}

		private void white() {
			if (!this.beginLine)
				addItem("");
			this.result.append(" ");
		}

		private void updateOrInsertOrDelete() {
			out();
			this.indent += 1;
			this.beginLine = false;
			if ("update".equals(this.lcToken)) {
				newline();
			}
			if ("insert".equals(this.lcToken))
				this.afterInsert = true;
		}

		private void select() {
			out();
			this.indent += 1;
			newline();
			this.parenCounts.addLast(new Integer(this.parensSinceSelect));
			this.afterByOrFromOrSelects.addLast(Boolean.valueOf(this.afterByOrSetOrFromOrSelect));
			this.parensSinceSelect = 0;
			this.afterByOrSetOrFromOrSelect = true;
		}

		private void out() {
			addItem("");
			this.result.append(this.token);
		}

		private void endNewClause() {
			if (!this.afterBeginBeforeEnd) {
				this.indent -= 1;
				if (this.afterOn) {
					this.indent -= 1;
					this.afterOn = false;
				}
				newline();
			}
			out();
			if (!"union".equals(this.lcToken)) {
				this.indent += 1;
			}
			newline();
			this.afterBeginBeforeEnd = false;
			this.afterByOrSetOrFromOrSelect = (("by".equals(this.lcToken)) || ("set".equals(this.lcToken))
					|| ("from".equals(this.lcToken)));
		}

		private void beginNewClause() {
			if (!this.afterBeginBeforeEnd) {
				if (this.afterOn) {
					this.indent -= 1;
					this.afterOn = false;
				}
				this.indent -= 1;
				newline();
			}
			out();
			this.beginLine = false;
			this.afterBeginBeforeEnd = true;
		}

		private void values() {
			this.indent -= 1;
			newline();
			out();
			this.indent += 1;
			newline();
			this.afterValues = true;
		}

		private void closeParen() {
			this.parensSinceSelect -= 1;
			if (this.parensSinceSelect < 0) {
				this.indent -= 1;
				this.parensSinceSelect = ((Integer) this.parenCounts.removeLast()).intValue();
				this.afterByOrSetOrFromOrSelect = ((Boolean) this.afterByOrFromOrSelects.removeLast()).booleanValue();
			}
			if (this.inFunction > 0) {
				this.inFunction -= 1;
				out();
			} else {
				if (!this.afterByOrSetOrFromOrSelect) {
					this.indent -= 1;
					newline();
				}
				out();
			}
			this.beginLine = false;
		}

		private void openParen() {
			if ((isFunctionName(this.lastToken)) || (this.inFunction > 0)) {
				this.inFunction += 1;
			}
			this.beginLine = false;
			if (this.inFunction > 0) {
				out();
			} else {
				out();
				if (!this.afterByOrSetOrFromOrSelect) {
					this.indent += 1;
					newline();
					this.beginLine = true;
				}
			}
			this.parensSinceSelect += 1;
		}

		private static boolean isFunctionName(String tok) {
			char begin = tok.charAt(0);
			boolean isIdentifier = (Character.isJavaIdentifierStart(begin)) || ('"' == begin);
			return (isIdentifier) && (!SqlFormateUtil.LOGICAL.contains(tok))
					&& (!SqlFormateUtil.END_CLAUSES.contains(tok)) && (!SqlFormateUtil.QUANTIFIERS.contains(tok))
					&& (!SqlFormateUtil.DML.contains(tok)) && (!SqlFormateUtil.MISC.contains(tok));
		}

		private static boolean isWhitespace(String token) {
			return " \n\r\f\t".indexOf(token) >= 0;
		}

		private void newline() {
			StringBuffer newLine = new StringBuffer();
			newLine.append("\n");
			this.result.append("\n");
			for (int i = 0; i < this.indent; i++) {
				this.result.append("    ");
				newLine.append("    ");
			}
			this.beginLine = true;
			addNewForArea(newLine.toString());
		}
		private void addItem(String newLine) {
			addWhereItem();
			addFromItem();
			addGroupByItem();
			addOrderByItem();
			addSelectItem();

		}
		private void addWhereItem() {
			if ("where".equals(this.lcToken) && this.indent == 1) {
				isFromStart = false;
				isWhereStart = true;
				whereItems = new ArrayList<>();
			}
			if (isWhereStart) {
				if (this.token.contains(SqlFormateUtil.prefix)) {
					conditionItems.add(this.token);
				}
				if (whereItems.size() > 0 && !isNewLineForWhere) {
					String param = whereItems.get(whereItems.size()-1);
					whereItems.set(whereItems.size()-1, param+" "+this.token);
				} else {
					if (!this.lcToken.equals("and") && !this.lcToken.equals("or") && !"where".equals(this.lcToken)) {
						whereItems.add(this.token);
					} else {
						whereItems.add("");
					}

				}
				isNewLineForWhere = false;
			}
		}
		private void addSelectItem() {
			if (isSelectStart && selectInit) {
				if (selectArea.length() < 1) {
					selectArea.append(this.token + " ");
				} else {
					selectArea.append(this.token);
				}
			}
		}
		private void addFromItem() {
			if (isFromStart) {
				if (this.lcToken.equals("select") || this.lcToken.equals("union") || this.lcToken.equals("join") || this.lcToken.equals("from")) {
					fromArea.append(this.token + " ");
				} else {
					fromArea.append(this.token);
				}

			}
		}
		private void addOrderByItem() {
			if (isOrderByStart) {
				if (END_CLAUSES.contains(this.lcToken)) {
					orderByArea.append(this.token + " ");
				} else {
					orderByArea.append(this.token);
				}
			}
		}
		private void addGroupByItem() {
			if (isGroupByStart) {
				if (END_CLAUSES.contains(this.lcToken)) {
					groupByArea.append(this.token + " ");
				} else {
					groupByArea.append(this.token);
				}
			}
		}
		private void addNewForArea(String newLine) {
			if (newLine == null || newLine.length() < 1) {
				return;
			}
			if (isGroupByStart) {
				groupByArea.append(newLine);
			} else if (isOrderByStart) {
				orderByArea.append(newLine);
			} else if (isFromStart) {
				fromArea.append(newLine);
			} else if (isSelectStart && selectInit) {
				selectArea.append(newLine);
			}
		}
		public List<String> getWhereItems() {
			return whereItems;
		}

		public List<String> getConditionItems() {
			return conditionItems;
		}

		public StringBuffer getSelectArea() {
			return selectArea;
		}

		public StringBuffer getFromArea() {
			return fromArea;
		}

		public StringBuffer getGroupByArea() {
			return groupByArea;
		}

		public StringBuffer getOrderByArea() {
			return orderByArea;
		}


	}
	public static String doValidation(Template template) {
		if (template == null) {
			return "";
		}
		StringBuffer buffer = new StringBuffer();
		buffer.append(template.getSelectArea()).append(" ").append(template.getFromArea()).append(" ")
				.append(QueryStringUtil.getNonEmpty(template.getWhereArea())).append(" ").append(QueryStringUtil.getNonEmpty(template.getGroupbyArea())).append(" ")
				.append(QueryStringUtil.getNonEmpty(template.getOrderbyArea()));
		String sql = buffer.toString();
		return doValidation(sql);
	}
	public static String doValidation(String sql) {

		try {
			String validateSql = sql;
			validateSql = validateSql.toUpperCase();
			if (validateSql.contains("DROP") || !validateSql.contains("SELECT")) {
				return "";
			}
			sql = sql.replaceAll("\\#\\{", prefix);
			sql = sql.replaceAll("\\}", suffix);
			Statement statement = (Statement) CCJSqlParserUtil.parse(sql);
		} catch (JSQLParserException e) {
			ByteArrayOutputStream baos = new ByteArrayOutputStream();
			e.printStackTrace(new PrintStream(baos));
			String exception = baos.toString();
			String regEx = "Encountered(.*)";
			Pattern pat = Pattern.compile(regEx);
			Matcher mat = pat.matcher(exception);
			while (mat.find()) {
				exception = mat.group(1);
			}
			// System.out.println(exception);
			String line = "";
			String regEx2 = "line (.*),";
			pat = Pattern.compile(regEx2);
			mat = pat.matcher(exception);
			while (mat.find()) {
				line = mat.group(1);
			}
			// System.out.println(line);

			int line_num = Integer.valueOf(line).intValue();
			int indexofcolumn = exception.indexOf("column");
			String errornumber = exception.substring(indexofcolumn + 7,
					exception.length() - 1);
			int error_num = Integer.valueOf(errornumber).intValue();
			System.out.println(error_num);

			String ERROR_location = "";
			if (error_num != 1) {
				String sql_sub = sql.substring(0, error_num - 2); // 发生错误位置前面的字符串
				// 错误信息单词往往处于错误位置的前一个地方单词
				// 获取错误位置两个前面两个空格之间的单词，并保存
				sql_sub = new StringBuilder(sql_sub).reverse().toString();
				int indexofspace = sql_sub.indexOf(" ");
				String sql_error = sql_sub.substring(0, indexofspace);
				sql_error = new StringBuilder(sql_error).reverse().toString();
				ERROR_location = "\"" + sql_error + "\"" + " at line "
						+ line_num + " at column " + error_num;
			} else {
				int indexofspace = sql.indexOf(" ");
				String sql_error = sql.substring(0, indexofspace);
				ERROR_location = "\"" + sql_error + "\"" + " at line "
						+ line_num + " at column " + error_num;
			}
			return ERROR_location; // 错误信息的返回
		}
		String result = SUCCESS_MESSAGE;
		return result; // Jsql可以解析，返回correct
	}
	public static Template replaceBack(Template template) {
		String whereArea = QueryStringUtil.getNonEmpty(template.getWhereArea());
		template.setWhereArea(replaceBack(whereArea));
		return template;
	}
	public static String replaceBack(String value) {
		value = value.replaceAll(SqlFormateUtil.prefix, "\\#\\{");
		value = value.replaceAll(SqlFormateUtil.suffix, "\\}");
		return value;
	}

}